package com.reserv.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.naming.NamingException;

import org.joda.time.Days;
import org.joda.time.LocalDate;

import com.reserv.db.ConnectionProvider;
import com.reserv.db.JDBCManager;
import com.reserv.util.TimestampUtil;
import com.reserv.vo.ReservVO;

public class ReservDAO implements DAO<ReservVO, Integer> {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	private static ReservDAO instance = new ReservDAO();
	public static ReservDAO getInstance() {
		return instance != null ? instance : new ReservDAO();
	}
	@Override
	public void createVO(ReservVO reserv) {
		String sql = "INSERT INTO reserv(sno, uid, pt, ppl, fee, rvDate) VALUES(?, ?, ?, ?, ?, ?)";
		
			try {
				conn = ConnectionProvider.getConnection();
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setInt(1, reserv.getSno());
				pstmt.setString(2, reserv.getUid());
				pstmt.setInt(3, reserv.getPt());
				pstmt.setInt(4, reserv.getPpl());
				pstmt.setInt(5, reserv.getFee());
				pstmt.setTimestamp(6, reserv.getRvDate());
				
				pstmt.executeUpdate();
			} catch (NamingException | SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCManager.closeAll(conn, pstmt, rs);
			}
	}
	@Override
	public ReservVO getVO(Integer p) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public List<ReservVO> getListVOs() {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public void updateVO(ReservVO v) {
		// TODO Auto-generated method stub

	}
	@Override
	public void deleteVO(Integer p) {
		// TODO Auto-generated method stub

	}
	public List<Timestamp> getReservList(Integer sno) {
		String sql1 = "SELECT rsDate, reDate, maxrv FROM service WHERE sno=?";

		TimestampUtil tsu = new TimestampUtil();
		List<Timestamp> list = new ArrayList<>();
		Timestamp rsDate = null;
		Timestamp reDate = null;
		int maxrv = 0;

		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql1);

			pstmt.setInt(1, sno);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				rsDate = rs.getTimestamp(1);
				reDate = rs.getTimestamp(2);
				maxrv = rs.getInt(3);
			}
			JDBCManager.closeAll(null, pstmt, rs);

			String sql2 = "SELECT COUNT(pt) FROM reserv WHERE sno=? and rvDate=?";

			LocalDate ld1 = new LocalDate(rsDate);
			LocalDate ld2 = new LocalDate(reDate);

			int days = Days.daysBetween(ld1, ld2).getDays();

			for(int i = 0; i < days; i++) {
				pstmt = conn.prepareStatement(sql2);

				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(rsDate.getTime());
				cal.add(Calendar.DATE, i);
				Timestamp ts = new Timestamp(cal.getTimeInMillis());

				pstmt.setInt(1, sno);
				pstmt.setTimestamp(2, ts);

				rs = pstmt.executeQuery();
				while(rs.next()) {
					if(rs.getInt(1) == maxrv) {
						list.add(ts);
					}
				}
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<Integer> getReservList(int sno, Timestamp ts) {
		String sql = "SELECT pt FROM reserv WHERE sno=? AND rvdate = ? ORDER BY pt;";
		List<Integer> list = null;
		try {
			conn = ConnectionProvider.getConnection();
			pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, sno);
			pstmt.setTimestamp(2, ts);

			rs = pstmt.executeQuery();
			if(rs.next()) {
				list = new ArrayList<>();
				do {
					list.add(rs.getInt(1));
				} while(rs.next());
			}
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCManager.closeAll(conn, pstmt, rs);
		}
		return list;
	}
}
